#pip install tushare
import tushare
#pip install PyMySQL
#import pymysql.cursors
import sqlite3
import time

#db = pymysql.connect("10.77.7.150","root", "my-secret-pw", "stock")
db = sqlite3.connect("finance.sqlite")

def recordPriceintoDB(db, code, start, end):
    dlist = tushare.get_k_data(code, start, end)
    d = dlist.to_dict()
    for k in d["date"]:
        try:
            sql = "INSERT INTO price (code, price, time) VALUES (?, ?, ?)"
            print code, str(d["close"][k]), d["date"][k], db.execute(sql, (code, str(d["close"][k]), d["date"][k]))
            db.commit()

        except Exception, e:
            print "except ",e
            db.commit()
            return 



if __name__ == "__main__":
    codes = [
            "000001",
            "002142",
            "600000",
            "600015",
            "600016",
            "600036",
            "601009",
            "601166",
            "601169",
            "601288",
            "601328",
            "601398",
            "601818",
            "601939",
            "601988",
            "601998",
            ]
    for code in codes:
        recordPriceintoDB(db, code, start="2011-01-01", end="2011-12-30")
        time.sleep(5)
        recordPriceintoDB(db, code, start="2012-01-01", end="2012-12-30")
        time.sleep(5)
        recordPriceintoDB(db, code, start="2013-01-01", end="2013-12-30")
        time.sleep(5)
        recordPriceintoDB(db, code, start="2014-01-01", end="2014-12-30")
        time.sleep(5)
        recordPriceintoDB(db, code, start="2015-01-01", end="2015-12-30")
        time.sleep(5)
        recordPriceintoDB(db, code, start="2016-01-01", end="2016-12-30")
        time.sleep(5)
        recordPriceintoDB(db, code, start="2017-01-01", end="2017-12-30")
        time.sleep(5)

#    print tushare.get_stock_basics(600016)
#    report = tushare.get_report_data(2017,3).to_dict()
#    print report

